clear
set type double
set more 1

capture log using log/TableA.log, replace
*produces Table A1 

*produces data for Table A1 on size distribution using 2018 employment data
****************************************

*emp2018
use ../data/emp_wages_annual_2012to2019, clear
	keep if year==2018
	keep vat emp 
	rename emp emp2018
save temp/emp2018, replace


use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	rename country cty_code
	merge m:1 vat cncode cty_code year month using ../data/xcurrencychoice_estdata, keepusing(Ix) nogen
	drop if missing(Ix)
	
	drop date *I
	drop if missing(valueX)
	drop if missing(currency)
	drop if cty_code=="EU"
	rename valueX value
	drop if missing(currency)
	keep if year==2018
	merge m:1 vat using ../data/manuf_firm, keep(match) nogen
			
	collapse (sum) xvalue=value , by(vat)
	merge m:1 vat using temp/emp2018, keep(match)
	
	sort emp 
		
	gen bin=1 if emp<=50
	replace bin =2 if emp<=100 & missing(bin)
	replace bin =3 if emp<=200 & missing(bin)
	replace bin =4 if emp<=350 & missing(bin)
	replace bin =5 if emp<=550 & missing(bin)
	replace bin =6 if emp<=850 & missing(bin)
	replace bin =7 if emp<=2000 & missing(bin)
	replace bin = 8 if missing(bin) 
	
	egen maxl=max(emp), by(bin)
	gen nfirms=1

	collapse (sum) xvalue nfirms, by(bin maxl)
	egen totxvalue=sum(xvalue)
	gen sh_exports=xvalue/totxvalue
	
	order bin nfirms maxl sh*
	keep bin nfirms maxl sh_exports
	
export excel using ../results/sumstats/TableA1, sheet(exports) firstrow(variables) replace keepcellfmt	




*********************
*import shares
******************************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	rename country cty_code
	merge m:1 vat cncode cty_code year month using ../data/mcurrencychoice_estdata, keepusing(Im) nogen
	drop if missing(Im)
	
	drop date *X
	drop if missing(valueI)
	drop if missing(currency)
	drop if cty_code=="EU"
	rename valueI value
	drop if missing(currency)
	keep if year==2018
	merge m:1 vat using ../data/manuf_firm, keep(match) nogen
		
	collapse (sum) mvalue=value , by(vat)
	merge m:1 vat using temp/emp2018, keep(match)
	
	sort emp 
		
	gen bin=1 if emp<=50
	replace bin =2 if emp<=100 & missing(bin)
	replace bin =3 if emp<=200 & missing(bin)
	replace bin =4 if emp<=350 & missing(bin)
	replace bin =5 if emp<=550 & missing(bin)
	replace bin =6 if emp<=850 & missing(bin)
	replace bin =7 if emp<=2000 & missing(bin)
	replace bin = 8 if missing(bin) 
	
	egen maxl=max(emp), by(bin)
	gen nfirms=1

	collapse (sum) mvalue nfirms, by(bin maxl)
	egen totmvalue=sum(mvalue)
	gen sh_imports=mvalue/totmvalue
	
	order bin nfirms maxl sh*
	keep bin nfirms maxl sh_imports
	
export excel using ../results/sumstats/TableA1, sheet(imports, replace) firstrow(variables) keepcellfmt

log close

